package com.rickpan.repository;

import com.rickpan.entity.VipUsageRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

/**
 * VIP功能使用记录数据访问接口
 */
@Repository
public interface VipUsageRecordRepository extends JpaRepository<VipUsageRecord, Long> {

    /**
     * 根据用户ID、功能代码和使用日期查找记录
     */
    Optional<VipUsageRecord> findByUserIdAndFeatureCodeAndUsageDate(Long userId, String featureCode, LocalDate usageDate);

    /**
     * 根据用户ID和功能代码查找使用记录
     */
    List<VipUsageRecord> findByUserIdAndFeatureCodeOrderByUsageDateDesc(Long userId, String featureCode);

    /**
     * 根据用户ID查找所有使用记录
     */
    List<VipUsageRecord> findByUserIdOrderByUsageDateDesc(Long userId);

    /**
     * 统计用户在指定时间范围内某功能的使用量
     */
    @Query("SELECT COALESCE(SUM(v.usageCount), 0) FROM VipUsageRecord v WHERE v.userId = :userId AND v.featureCode = :featureCode AND v.usageDate BETWEEN :startDate AND :endDate")
    Integer sumUsageByUserAndFeatureAndDateRange(@Param("userId") Long userId, 
                                               @Param("featureCode") String featureCode,
                                               @Param("startDate") LocalDate startDate, 
                                               @Param("endDate") LocalDate endDate);

    /**
     * 统计用户今日某功能的使用量
     */
    @Query("SELECT COALESCE(SUM(v.usageCount), 0) FROM VipUsageRecord v WHERE v.userId = :userId AND v.featureCode = :featureCode AND v.usageDate = :date")
    Integer sumTodayUsageByUserAndFeature(@Param("userId") Long userId, 
                                        @Param("featureCode") String featureCode,
                                        @Param("date") LocalDate date);

    /**
     * 统计用户本月某功能的使用量
     */
    @Query("SELECT COALESCE(SUM(v.usageCount), 0) FROM VipUsageRecord v WHERE v.userId = :userId AND v.featureCode = :featureCode AND YEAR(v.usageDate) = :year AND MONTH(v.usageDate) = :month")
    Integer sumMonthlyUsageByUserAndFeature(@Param("userId") Long userId, 
                                          @Param("featureCode") String featureCode,
                                          @Param("year") int year, 
                                          @Param("month") int month);

    /**
     * 查找指定日期范围内的使用记录
     */
    @Query("SELECT v FROM VipUsageRecord v WHERE v.usageDate BETWEEN :startDate AND :endDate ORDER BY v.usageDate DESC")
    List<VipUsageRecord> findUsageRecordsBetween(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    /**
     * 统计功能使用总量 (按功能代码)
     */
    @Query("SELECT v.featureCode, SUM(v.usageCount) FROM VipUsageRecord v WHERE v.usageDate BETWEEN :startDate AND :endDate GROUP BY v.featureCode")
    List<Object[]> getFeatureUsageStatistics(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    /**
     * 删除过期的使用记录 (超过指定天数)
     */
    @Query("DELETE FROM VipUsageRecord v WHERE v.usageDate < :cutoffDate")
    int deleteExpiredRecords(@Param("cutoffDate") LocalDate cutoffDate);
}